package com.gitee.aurora.server.dao.impl;


import com.gitee.aurora.core.comm.AuroraJdbcTemplate;
import com.gitee.aurora.server.dao.PtConfigDao;

import com.gitee.aurora.utils.PageParam;
import com.gitee.aurora.utils.Pagination;
import com.gitee.aurora.server.vo.PtConfig;
import com.gitee.aurora.server.vo.PtcjConfig;
import com.gitee.aurora.server.vo.SqlConfigItem;
import com.gitee.aurora.server.vo.TableConfig;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

@Repository
public class PtConfigDaoImpl implements PtConfigDao {

    @Autowired
    private AuroraJdbcTemplate auroraJdbcTemplate;
    @Override
    public List<PtcjConfig> queryPtcjConfig() throws Exception {



        String sql = "SELECT " +
                "g.S_ID AS sId," +
                "g.S_CODE AS sCode," +
                "g.S_CODE_NAME AS sCodeName," +
                "g.PT_CJ AS ptCj," +
                "g.PT_IP AS ptIp," +
                "g.F_CODE AS fCode," +
                "g.F_CODE_NAME AS fCodeName," +
                "g.F_CODE_ORDER AS fCodeOrder," +
                "  t.PT_SQL AS ptSql," +
                "  t.PT_TABLE AS ptTable,t.PT_CODE AS ptCode" +
                " FROM " +
                "TAB_PTCJ_CONFIG  g LEFT " +
                "JOIN TAB_PT_CONFIG  t on g.S_CODE=t.S_CODE";

        return auroraJdbcTemplate.queryForMapToList(sql,PtcjConfig.class);
    }

    @Override
    public List<TableConfig> queryTable() throws Exception {
        String sql = "select TABLE_CODE as tableCode,TABLE_CODE_NAME AS tableCodeName from TAB_TABLE_CONFIG";
        return auroraJdbcTemplate.queryForMapToList(sql,TableConfig.class);
    }


    @Override
    public List<SqlConfigItem> queryPtXl() throws Exception {
        String sql = "SELECT TYPE_CODE  as typeCode,TYPE_NAME as typeName FROM TAB_SQL_CONFIG_ITEM   GROUP BY TYPE_CODE ,TYPE_NAME ";
        return auroraJdbcTemplate.queryForMapToList(sql, SqlConfigItem.class);
    }

    @Override
    public List<SqlConfigItem> queryPtLx() throws Exception {
        String sql = "SELECT  DICT_CODE AS dictCode ,dictCodeName FROM TAB_SQL_CONFIG_ITEM   GROUP BY DICT_CODE ,DICT_CODE_NAME";
        return auroraJdbcTemplate.queryForMapToList(sql,SqlConfigItem.class);
    }

    @Override
    public Pagination queryPtConfigService(PtConfig ptConfig, PageParam pageParam) {
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select S_ID AS sId, PT_XL_TYPE as ptXlType ,PT_XL_TYPE_NAME as ptXlTypeName,PT_SQL as ptSql,PT_TABLE as ptTable,DATE_FORMAT(DT_CREATE_TIME,'%Y-%m-%d %H:%i:%s') as dtCreateTime" +
                ",DATE_FORMAT(DT_UPDATE_TIME,'%Y-%m-%d %H:%i:%s') as  dtUpdateTime ,PT_CODE as ptCode ,PT_CODE_NAME as ptCodeName ,S_CODE as sCode from TAB_PT_CONFIG where 1=1 ");
        int[] types = new int[]{java.sql.Types.INTEGER, java.sql.Types.INTEGER, Types.VARCHAR};
        if(ptConfig!=null){
            if(ptConfig.getPtXlType()!=null){
                sql.append("and  PT_XL_TYPE = ?");
                params.add(ptConfig.getPtXlType());
            }
            if(ptConfig.getPtCode()!=null){
                sql.append("and  PT_CODE= ?");
                params.add(ptConfig.getPtCode());
            }
            if(ptConfig.getSCode()!=null){
                sql.append("and  S_CODE = ?");
                params.add(ptConfig.getSCode());
            }

        }
        return  new Pagination(sql.toString(),params.toArray(),pageParam,auroraJdbcTemplate);
    }

    @Override
    public int deletePtConfigService(PtConfig ptConfig) {
        String sql = "delete  from  TAB_PT_CONFIG  where  S_ID=?";
        return auroraJdbcTemplate.update(sql, new Object[]{ptConfig.getSId()});
    }

    @Override
    public int insertPtConfigService(PtConfig ptConfig) {
        String sql = "insert into  TAB_PT_CONFIG (PT_XL_TYPE,PT_XL_TYPE_NAME,PT_SQL,PT_TABLE,PT_CODE,PT_CODE_NAME,S_CODE) values (?,?,?,?,?,?,?)";
        return auroraJdbcTemplate.update(sql, new Object[]{ptConfig.getPtXlType(),ptConfig.getPtXlTypeName(),ptConfig.getPtSql(),ptConfig.getPtTable(),ptConfig.getPtCode(),ptConfig.getPtCodeName(),ptConfig.getSCode()});
    }

    @Override
    public int updatePtConfigService(PtConfig ptConfig) {
        List<Object> params = new ArrayList<>();
        String sql = "update TAB_PT_CONFIG  set PT_XL_TYPE=? ,PT_XL_TYPE_NAME=?,PT_SQL=?,PT_TABLE=?, PT_CODE=?,PT_CODE_NAME=?,S_CODE=?   where  S_ID=?";
        params.add(ptConfig.getPtXlType());
        params.add(ptConfig.getPtXlTypeName());
        params.add(ptConfig.getPtSql());
        params.add(ptConfig.getPtTable());
        params.add(ptConfig.getPtCode());
        params.add(ptConfig.getPtCodeName());
        params.add(ptConfig.getSCode());
        params.add(ptConfig.getSId());
        return auroraJdbcTemplate.update(sql, params.toArray());
    }

    @Override
    public List<PtcjConfig> queryPtxjInfo(String sCode) throws Exception {
        List<Object> params = new ArrayList<>();
        int[] types = new int[]{java.sql.Types.VARCHAR};
        StringBuilder sql  =new StringBuilder( " SELECT "  +
                "g.S_ID AS sId," +
                "g.S_CODE AS sCode," +
                "g.S_CODE_NAME AS sCodeName," +
                "g.PT_CJ AS ptCj," +
                "g.PT_IP AS ptIp," +
                "g.F_CODE AS fCode," +
                "g.F_CODE_NAME AS fCodeName," +
                "g.F_CODE_ORDER AS fCodeOrder," +
                "t.PT_SQL AS ptSql," +
                "t.PT_TABLE AS ptTable,t.PT_CODE AS ptCode," +
                "t.PT_CODE as ptCode," +
                "t.JC_COLUMN as jcColunmn,"+
                "t.JC_COLUMN_NAME as jcColunmnName"+
                " FROM " +
                " TAB_PTCJ_CONFIG  g LEFT " +
                "JOIN TAB_PT_CONFIG  t on g.S_CODE=t.S_CODE where 1=1");
        if(sCode!=null){
                sql.append(" and  g.F_CODE=?");
                params.add(sCode);
        }
               sql.append(" group by g.S_CODE");
        return auroraJdbcTemplate.queryForMapToList(sql.toString(),params.toArray(),types,PtcjConfig.class);
    }


    @Override
    public List<PtConfig> querySql(PtConfig ptConfig) throws Exception {
        List<Object> params = new ArrayList<>();
        //int[] types = new int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR};
        StringBuilder sql  =new StringBuilder( "SELECT  PT_SQL ptSql,JC_COLUMN as jcColunmn,JC_COLUMN_NAME jcColunmnName  FROM  TAB_PT_CONFIG where 1=1");
        if(ptConfig.getPtTable()!=null){
            sql.append(" and  PT_TABLE=?");
            params.add(ptConfig.getPtTable());
        }
        if(ptConfig.getPtXlType()!=null){
            sql.append(" and  PT_XL_TYPE=?");
            params.add(ptConfig.getPtXlType());
        }
        if(ptConfig.getPtCode()!=null){
            sql.append(" and  PT_CODE=?");
            params.add(ptConfig.getPtCode());
        }
        if(ptConfig.getCode()!=null){
            sql.append(" and  S_CODE=?");
            params.add(ptConfig.getCode());
        }

        return  auroraJdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PtConfig.class), params.toArray());


    }

    @Override
    public List<PtcjConfig> queryPtxjinfoList(PtcjConfig ptcjConfig) throws Exception {
        List<Object> params = new ArrayList<>();
        StringBuilder sql  =new StringBuilder( " SELECT "  +
                "g.S_ID AS sId," +
                "g.S_CODE AS sCode," +
                "g.S_CODE_NAME AS sCodeName," +
                "g.PT_CJ AS ptCj," +
                "g.PT_IP AS ptIp," +
                "g.F_CODE AS fCode," +
                "g.F_CODE_NAME AS fCodeName," +
                "g.F_CODE_ORDER AS fCodeOrder," +
                "t.PT_SQL AS ptSql," +
                "t.PT_TABLE AS ptTable,t.PT_CODE AS ptCode," +
                "t.PT_CODE as ptCode," +
                "t.JC_COLUMN as jcColunmn,"+
                "t.JC_COLUMN_NAME as jcColunmnName"+
                " FROM " +
                " TAB_PTCJ_CONFIG  g LEFT " +
                "JOIN TAB_PT_CONFIG  t on g.S_CODE=t.S_CODE where 1=1");
        if(StringUtils.isNotEmpty(ptcjConfig.getCode())){
            sql.append(" and  g.F_CODE=?");
            params.add(ptcjConfig.getCode());
        }
        if(ptcjConfig.getPtCode()!=null){
            sql.append(" and  t.PT_CODE=?");
            params.add(ptcjConfig.getPtCode());
        }

        sql.append(" group by g.S_CODE");
        return  auroraJdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PtcjConfig.class), params.toArray());
    }


}
